** Capability Rankings (Bins)
** This do-file is necessary for conducting robustness check shown in Table A18 

/* input 
${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta
${output_data}quota_mex_weight_hs6.dta
*/

/* output
${output_data}mexico_for_analysis_04_07_hs6_restrict_quantile5.dta
${output_data}mexico_for_analysis_04_07_hs6_restrict_quantile10.dta
${output_data}mexico_for_analysis_04_07_hs6_restrict_quantile20.dta
${output_data}US_for_analysis_04_07_hs6_restrict_quantile5.dta
${output_data}US_for_analysis_04_07_hs6_restrict_quantile10.dta
${output_data}US_for_analysis_04_07_hs6_restrict_quantile20.dta
*/


* Create data for making rankings in 2004
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", replace
keep rfc hs6 year newclid2  value_exporter_importer
keep if year==2004
label var newclid2 "importer id"
label var value_exporter_importer "exports of hs6 between exporter and importer"
label var rfc "exporter id"
label var hs6 "hs6"
collapse (sum) value_exporter_importer, by(rfc newclid2 hs6)
tostring newclid2, generate(importer_id)
gen hs6_importer_id=hs6+"_"+ importer_id
gen hs6_exporter_id=hs6+"_"+ rfc
save "${temp_data_bin}data_for_ranking_hs6.dta", replace


* Create ranking data by 5, 10, 20 bins
foreach x in 5 10 20{

*************************
*Step 1 Create importer rank by hs6
*************************

* Identify partner with highest match value
use "${temp_data_bin}data_for_ranking_hs6.dta", clear
gsort hs6 -value_exporter_importer newclid2
egen mv_rank=xtile(value_exporter_importer), by(hs6) n(`x') 
keep hs6_importer_id hs6_exporter_id mv_rank 
tostring mv_rank, generate(mv_rank_s)
gen hs6_importer_id_mv_rank= hs6_importer_id+"_"+ mv_rank_s
keep hs6_exporter_id hs6_importer_id_mv_rank
sort hs6_importer_id_mv_rank
rename hs6_exporter_id mv_partner
label var mv_partner "id of exporter with whom importer has largest match value"
save "${temp_data_bin}data_for_ranking_hs6_mv_partner.dta", replace

* Identify partner with highest match value
use "${temp_data_bin}data_for_ranking_hs6.dta", clear
gsort hs6 -value_exporter_importer newclid2
egen mv_rank=xtile(value_exporter_importer), by(hs6) n(`x')
collapse (max) mv_rank, by(hs6_importer_id)
tostring mv_rank, generate(mv_rank_s)
gen hs6_importer_id_mv_rank= hs6_importer_id+"_"+ mv_rank_s
drop mv_rank_s
gen hs6=substr(hs6_importer_id,1,6)
gsort hs6 mv_rank
label var mv_rank "ranking of match values in hs6 for importer"
drop hs6
sort hs6_importer_id_mv_rank
merge 1:m hs6_importer_id_mv_rank using "${temp_data_bin}data_for_ranking_hs6_mv_partner.dta"
keep if _merge==3
drop _merge  hs6_importer_id_mv_rank
sort hs6_importer_id
save "${temp_data_bin}data_for_ranking_hs6_mv_rank.dta", replace

** Make hs6_importer_id be unique
use "${temp_data_bin}data_for_ranking_hs6_mv_rank.dta", clear
sort hs6_importer_id
drop if hs6_importer_id==hs6_importer_id[_n+1] 
save "${temp_data_bin}importer_ranking_hs6.dta", replace

*************************
*Step 2 Create Mexican downgrading 6-digit level data
*************************

* Create data on export values between importers and exporters
use "${output_data}matched_exp_imp_hs6_junedecember_maquila_restrict.dta", clear
keep rfc hs6 year newclid2 value_exporter_importer
collapse (sum) value_exporter_importer, by(rfc newclid2 year hs6)
gen hs6_exporter_id= hs6+"_"+ rfc
tostring newclid2, generate(importer_id)
gen hs6_importer_id=hs6+"_"+ importer_id
save "${temp_data_bin}exporter_importer_hs6_restrict.dta", replace

* Create data for rankings 
use "${temp_data_bin}exporter_importer_hs6_restrict.dta", clear
tostring year, generate(year_s)
gen hs_exporter_id_year= hs6_exporter_id+"_"+year_s
gsort hs_exporter_id_year -value_exporter_importer
by hs_exporter_id_year: gen importer_rank=_n
save "${temp_data_bin}down_grading_hs6.dta", replace

* Identify main partner in 2004 2007
foreach j in 2004 2007{
use "${temp_data_bin}down_grading_hs6.dta", clear 
keep if importer_rank==1 
keep if year==`j'
keep hs6_exporter_id  hs6_importer_id
sort hs6_importer_id
merge m:m hs6_importer_id using "${temp_data_bin}importer_ranking_hs6.dta"
drop if _merge!=3
rename hs6_importer_id main`j'_id
rename mv_rank mv_rank`j'
keep hs6_exporter_id main`j'_id mv_rank`j'
save "${temp_data_bin}main`j'_hs6.dta", replace
}

* Create data on 2004-2007 for Mexico 
* Merge of rankings in 2004 and 200`j' 
use "${temp_data_bin}down_grading_hs6.dta", clear 
keep if year==2004
keep hs6 rfc hs6_exporter_id
duplicates drop
sort hs6_exporter_id
merge 1:1 hs6_exporter_id using "${temp_data_bin}main2004_hs6.dta"  
keep if _merge==3
drop _merge
sort hs6_exporter_id
merge 1:1 hs6_exporter_id using "${temp_data_bin}main2007_hs6.dta"
drop if _merge==2
drop _merge
sort hs6
* Identify whether the ranking was downgrading
sort hs6
gen mv_rank_d0704= mv_rank2007 -mv_rank2004
label var mv_rank_d0704 "mv_rank_2007-mv_rank_2004"
gen i_mv_rank_d0704=1 if mv_rank_d0704<0
replace  i_mv_rank_d0704=0 if mv_rank_d0704>=0
replace  i_mv_rank_d0704=. if mv_rank_d0704==.
* Create data for analysis (Mexico)
keep hs6 main2004_id hs6_exporter_id mv_rank_d0704 mv_rank2004 mv_rank2007 i_mv_rank_d0704
gen newclid2=substr(main2004_id, 8, length(main2004_id))
destring newclid2, replace
sort hs6
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge
gen hs2=substr(hs6,1,2)
duplicates drop hs6_exporter_id if hs6_exporter_id!="", force
save "${output_data}mexico_for_analysis_04_07_hs6_restrict_quantile`x'.dta", replace


*************************
*Step 3 Create exporter rank by hs6
*************************

* Identify partner with highest match value
use "${temp_data_bin}data_for_ranking_hs6.dta", replace
egen e_mv_rank=xtile(value_exporter_importer), by(hs6) n(`x')
keep hs6_importer_id hs6_exporter_id e_mv_rank 
tostring e_mv_rank, generate(e_mv_rank_s)
gen hs6_exporter_id_e_mv_rank= hs6_exporter_id+"_"+ e_mv_rank_s
keep hs6_exporter_id hs6_exporter_id_e_mv_rank
sort hs6_exporter_id_e_mv_rank		
rename hs6_exporter_id e_mv_partner
label var e_mv_partner "id of exporter's main importer"
save "${temp_data_bin}data_for_ranking_hs6_mv_partner_exp.dta", replace
		
* Create ranking of match values in 2004 (base year)
use "${temp_data_bin}data_for_ranking_hs6.dta", clear
gsort hs6 -value_exporter_importer rfc
egen e_mv_rank=xtile(value_exporter_importer), by(hs6) n(`x')
collapse (max) e_mv_rank, by(hs6_exporter_id)
tostring e_mv_rank, generate(e_mv_rank_s)
gen hs6_exporter_id_e_mv_rank= hs6_exporter_id+"_"+ e_mv_rank_s
drop e_mv_rank_s
gen hs6=substr(hs6_exporter_id,1,6)
label var e_mv_rank "ranking of match values in hs6 for exporter"
drop hs6
sort hs6_exporter_id_e_mv_rank
merge 1:m hs6_exporter_id_e_mv_rank using "${temp_data_bin}data_for_ranking_hs6_mv_partner_exp.dta"
keep if _merge==3
drop _merge  hs6_exporter_id_e_mv_rank
sort hs6_exporter_id
save "${temp_data_bin}data_for_ranking_hs6_mv_rank_exp.dta", replace

** Make hs6_exporter_id be unique
use "${temp_data_bin}data_for_ranking_hs6_mv_rank_exp.dta", clear
sort hs6_exporter_id
drop if hs6_exporter_id==hs6_exporter_id[_n-1] 
save "${temp_data_bin}exporter_ranking_hs6.dta", replace


*************************
*Step 4 Create US upgrading 6-digit level restrict data
*************************

* Create data for rankings
use "${temp_data_bin}exporter_importer_hs6_restrict.dta", clear
tostring year, generate(year_s)
gen hs_importer_id_year= hs6_importer_id+"_"+year_s
gsort hs_importer_id_year -value_exporter_importer
by hs_importer_id_year: gen exporter_rank=_n
save "${temp_data_bin}up_grading_hs6.dta", replace

* Identify main partner in 2004 2007 
foreach j in 2004 2007 {
use "${temp_data_bin}up_grading_hs6.dta", clear 
keep if exporter_rank==1 
keep if year==`j'
keep hs6_exporter_id  hs6_importer_id
sort hs6_exporter_id
merge m:1 hs6_exporter_id using "${temp_data_bin}exporter_ranking_hs6.dta"
drop if _merge!=3
drop _merge
rename hs6_exporter_id main`j'_id
rename e_mv_rank e_mv_rank`j'
keep hs6_importer_id main`j'_id e_mv_rank`j' 
save "${temp_data_bin}main`j'_exp_hs6.dta", replace
}

* Merge of rankings in 2004 and 2007
use "${temp_data_bin}up_grading_hs6.dta", clear 
keep if year==2004
keep hs6 importer_id  hs6_importer_id
duplicates drop
sort hs6_importer_id
merge 1:1 hs6_importer_id using "${temp_data_bin}main2004_exp_hs6.dta"
drop if _merge==2
drop _merge
sort hs6_importer_id
merge 1:1 hs6_importer_id using "${temp_data_bin}main2007_exp_hs6.dta"
drop if _merge==2
drop _merge
sort hs6
* Identify whether the ranking was upgrading
sort hs6
gen e_mv_rank_d0704= e_mv_rank2007 -e_mv_rank2004
gen i_e_mv_rank_d0704=0 if e_mv_rank_d0704<=0
replace  i_e_mv_rank_d0704=1 if e_mv_rank_d0704>0
replace  i_e_mv_rank_d0704=. if e_mv_rank_d0704==.
label var e_mv_rank_d0704 "e_mv_rank_2007-e_mv_rank_2004"
label var i_e_mv_rank_d0704 "1 if e_mv_rank_d0704<0 (upgrade) and 0 otherwise"
* Create data for main analysis (US)
keep hs6 main2004_id hs6_importer_id e_mv_rank_d0704 i_e_mv_rank_d0704 e_mv_rank2004 e_mv_rank2007
gen newclid2=substr(hs6_importer_id, 8, length(main2004_id))
destring newclid2, replace
sort hs6
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge
gen hs2=substr(hs6,1,2)
duplicates drop hs6_importer_id if hs6_importer_id!="", force
save "${output_data}US_for_analysis_04_07_hs6_restrict_quantile`x'.dta", replace
}
